Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# NEW_TIME

Syntax

Description of the illustration new_time.gif

Purpose

`NEW_TIME` returns the date and time in time zone `timezone2` when date and time in time zone `timezone1` are `date`. Before using this function, you must set the `NLS_DATE_FORMAT` parameter to display 24-hour time. The return type is always `DATE`, regardless of the datatype of `date`.

Note: This function takes as input only a limited number of time zones. You can have access to a much greater number of time zones by combining the `FROM_TZ` function and the datetime expression. See FROM_TZ and the example for "Datetime Expressions".

The arguments `timezone1` and `timezone2` can be any of these text strings:

- AST, ADT: Atlantic Standard or Daylight Time

- BST, BDT: Bering Standard or Daylight Time

- CST, CDT: Central Standard or Daylight Time

- EST, EDT: Eastern Standard or Daylight Time

- GMT: Greenwich Mean Time

- HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.

- MST, MDT: Mountain Standard or Daylight Time

- NST: Newfoundland Standard Time

- PST, PDT: Pacific Standard or Daylight Time

- YST, YDT: Yukon Standard or Daylight Time

Examples

The following example returns an Atlantic Standard time, given the Pacific Standard time equivalent:

    ALTER SESSION SET NLS_DATE_FORMAT =
       'DD-MON-YYYY HH24:MI:SS';

    SELECT NEW_TIME(TO_DATE(
       '11-10-99 01:23:45', 'MM-DD-YY HH24:MI:SS'),
       'AST', 'PST') "New Date and Time" FROM DUAL;

    New Date and Time
    --------------------
    09-NOV-1999 21:23:45